package jk.zmn.sorm.core;

import jk.zmn.sorm.bean.ColumnInfo;
import jk.zmn.sorm.bean.TableInfo;
import jk.zmn.sorm.utils.JdbcUtils;
import jk.zmn.sorm.utils.ReflectUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
*
* @date 2018/6/15 16:49
 *
 * 负责查询，对外提供服务的核心类
*/
public abstract class Query {


    /**
    * 执行sql语句
    * @date 2018/6/15 16:50
    * @param sql  sql语句
    * @param params  参数
    * @return int  SQL影响的行数
     *
    */
    public int executeDML(String sql,Object[] params){

        Connection connection = DBManager.getConnection();
        int count = 0;

        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);

            JdbcUtils.handlerParams(ps,params);
            System.out.println(ps);

            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBManager.close(connection,ps);
        }
        System.out.println("count:"+count);
        return count;
    }


    /**
    *   将一个对象存储到数据库
    * @date 2018/6/15 16:52
    * @param object  要存储的对象
    * @return void
    */
    public void insert(Object object){
        // insert into logs(a,b) values (?,?)
        //得到类对应的表信息
        Class<?> aClass = object.getClass();
        TableInfo tableInfo = TableContext.poClassTableMap.get(aClass);
        StringBuilder sb = new StringBuilder("insert into "+tableInfo.getName()+"(");
        //得到属性
        Field[] fields = aClass.getDeclaredFields();

        ArrayList<Object> fieldValueList = new ArrayList<>();
        for (Field field : fields){
            String name = field.getName();
            Object value = ReflectUtils.invokeGet(object, name);
            if (value!=null){
                sb.append(name+",");
                fieldValueList.add(value);
            }
        }
        //将最后一个,换成）
        sb.setCharAt(sb.length()-1,')');
        sb.append(" values(");

        for (int i=0;i<fieldValueList.size();i++){
            sb.append("?,");
        }

        sb.setCharAt(sb.length()-1,')');

        executeDML(sb.toString(),fieldValueList.toArray());
    }



    /**
    *  删除一个对象
    * @date 2018/6/15 16:54
    * @param object 要移除的对象
    * @return void
    */
    public void delete(Object object){

        Class<?> aClass = object.getClass();

        TableInfo tableInfo = TableContext.poClassTableMap.get(aClass);
        //得到表的主键
        ColumnInfo onlyPriKey = tableInfo.getOnlyPriKey();

        String sql = "delete from "+tableInfo.getName()+" where "+onlyPriKey.getName()+"=?";

        //反射调用get方法，得到属性的值
        Object o = ReflectUtils.invokeGet(object, onlyPriKey.getName());
        executeDML(sql,new Object[]{o});

    }
    /**
    *  删除类 对应的表中的数据，删除该id的对象
    * @date 2018/6/15 16:55
    * @param clazz  类对象
    * @param id  主键
    * @return void
    */
    public void delete(Class clazz,Object id) {
        // delete from logs where id=?

        TableInfo tableInfo = TableContext.poClassTableMap.get(clazz);
        ColumnInfo onlyPriKey = tableInfo.getOnlyPriKey();
        String sql = "delete from "+tableInfo.getName()+" where "+onlyPriKey.getName()+"=?";
        executeDML(sql.toString(),new Object[]{id});
    }

    /**
    *  更新对象字段的信息
    * @date 2018/6/15 16:57
    * @param object  对象
    * @param fieldNames  多个字段
    * @return void
    */
    public void update(Object object,String[] fieldNames){

        //obj{"uanme","pwd"}-->update 表名  set uname=?,pwd=? where id=?
        Class c = object.getClass();
        List<Object> params = new ArrayList<Object>();   //存储sql的参数对象
        TableInfo tableInfo = TableContext.poClassTableMap.get(c);
        ColumnInfo priKey = tableInfo.getOnlyPriKey();   //获得唯一的主键
        StringBuilder sql  = new StringBuilder("update "+tableInfo.getName()+" set ");

        for(String fname:fieldNames){
            Object fvalue = ReflectUtils.invokeGet(object,fname);
            params.add(fvalue);
            sql.append(fname+"=?,");
        }
        sql.setCharAt(sql.length()-1, ' ');
        sql.append(" where ");
        sql.append(priKey.getName()+"=? ");

        params.add(ReflectUtils.invokeGet(object,priKey.getName()));    //主键的值

        executeDML(sql.toString(), params.toArray());
    }


    /**
    *   根据参数，查询指定的数据，多行记录，单行记录可直接get(0)
    * @date 2018/6/15 16:59
    * @param sql  sql语句
    * @param clazz   类对象
    * @param params   sql语句参数
    * @return java.util.List
    */
    public List queryRows(String sql,Class clazz,Object[] params){
        Connection connection = DBManager.getConnection();
        PreparedStatement ps = null;
        List<Object> rows = new ArrayList<Object>();
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement(sql);
            JdbcUtils.handlerParams(ps,params);
            resultSet = ps.executeQuery();
            //得到返回结果又多少列
            ResultSetMetaData metaData = resultSet.getMetaData();

            while (resultSet.next()){

                Object o = clazz.newInstance();

                for (int i=0;i<metaData.getColumnCount();i++){
                    //得到每一列的名称
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object columnValue = resultSet.getObject(i + 1);
                    ReflectUtils.invokeSet(o, columnLabel, columnValue);
                }

                rows.add(o);
            }

            return rows;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBManager.close(connection,ps);
        }
        return null;
    }



    /**
    *   查询某个字段的数据
    * @date 2018/6/15 17:05
    * @param sql  sql语句
    * @param params   参数
    * @return java.lang.Object 封装查询到的数据
    */
    public Object queryValue(String sql,Object[] params){

        Connection connection = DBManager.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Object o =null;
        try {
            ps = connection.prepareStatement(sql);
            JdbcUtils.handlerParams(ps,params);
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                o = resultSet.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            DBManager.close(connection,ps);
        }
        return o;
    }
}
